                                                                                                                                                                                                                                                                             *Chris Walters
*This program does a basic cleanup on the state level SIMS files
*files from the state have slight variations, thus each year is cleaned separately rather than in a loop
*DESE provides files in SPSS format and they must be converted to Stata

*NOTE A files are OCTOBER
*D files are end of year "eoy"
*update to go up to 2023
*include code to convert from SPSS to Stata

clear all


# delimit ;
set more off;

local raw "$raw\SIMS";
local saves "$raw\saves";
cd "`raw'";

******************;
*SWITCHES ;
	forvalues i=1/9 {;
	local end0`i' = 1;
	};

	forvalues i=10/22 {;
	local end`i' = 1;
	};
	
	local end22 1;
	
********************;
set seed 12345;

* convert dta from spss to dta;
forvalues i = 2/14 {;
    local j : display %02.0f `i';
    import spss using "`raw'\SIMS`j'D.sav", clear;
    save "`raw'\simseoy`j'.dta", replace;
};

forvalues i = 2/14 {;
    local j : display %02.0f `i';
    import spss using "`raw'\SIMS`j'A.sav", clear;
    save "`raw'\simsoct`j'.dta", replace;
};

forvalues i = 15/21 {;
    local j : display %02.0f `i';
    import spss using "`raw'\SIMS`j'D with ECODIS.sav", clear;
    save "`raw'\simseoy`j'.dta", replace;
};

forvalues i = 15/21 {;
    local j : display %02.0f `i';
    import spss using "`raw'\SIMS`j'A with ECODIS.sav", clear;
    save "`raw'\simsoct`j'.dta", replace;
};

import spss using "`raw'\SIMS22D with LOW_INCOME_with ECODIS_old_for Researchers.sav", clear ;
save "`raw'\simseoy22.dta", replace ;

import spss using "`raw'\SIMS22A with LOW_INCOME_with ECODIS_old_for Researchers.sav", clear ;
save "`raw'\simsoct22.dta", replace ;

import spss using "`raw'\SIMS23A with LOW_INCOME_with ECODIS_old_for Researchers.sav", clear ;
save "`raw'\simsoct23.dta", replace ;

* macros;
local strings gender race rfe town_res school attend low_inc lep sped org_code grade;
local strings2 gender race rfe town_res school attend low_inc lep sped org_code spedtype spedlev title1 grade;
local missing gender race attend low_inc lep;

if `end02'==1{;
use "`raw'\simseoy02.dta";
*sample 10
rename *, lower ;
keep lasid sasid fname mname lname dob gender race  rfe town_re school grade attend low_inc lep sped org_code;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename town_re town_res;
gen time="E";
replace sped="41" if sped=="4i";
gen year=2002;
sort sasid time year;
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
foreach l of local strings {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	destring sasid, replace;

compress;
save "`saves'\end02.dta", replace;
clear;
};


if `end03'==1{;
use "`raw'\simseoy03.dta";
rename *, lower ;
keep lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
gen time="E";
replace sped="41" if sped=="4i";
gen year=2003;
sort sasid time year;
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
foreach l of local strings{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	destring sasid, replace;
compress;
save "`saves'\end03.dta", replace;
clear;
};


if `end04'==1{;
use "`raw'\simseoy04.dta";
rename *, lower ;
keep org_code school sasid md_lasid fname mname lname dob md_gender race rfe town_res grade attend low_inc 
	lep sped spd_sped_prmy_disb_code sln_sped_lvl_need_code ti1_title1_code  md_days_in_school_susp
	md_days_out_school_susp md_days_in_truancy;
rename md_lasid lasid; rename md_gender gender; rename  ti1_title1_code title1; rename spd_sped_prmy_disb_code spedtype;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sln_sped_lvl_need_code spedlev; rename md_days_in_school_susp inschsusp;  rename md_days_out_school_susp 
outschsusp; rename md_days_in_truancy daystruant;
order  lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
gen time="E";
replace sped="41" if sped=="4i";
gen year=2004;
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
sort sasid time year;
foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	destring sasid, replace;
compress;
save "`saves'\end04.dta", replace;
clear;
};

if `end05'==1{;
use "`raw'\simseoy05.dta";
rename *, lower ;
keep org_code md_lasid sasid fname mname lname dob md_gender race rfe town_res school grade attend low_inc 
	lep sped spd_sped_prmy_disb_code sln_sped_lvl_need_code ti1_title1_code md_days_in_school_susp 
	md_days_out_school_susp md_days_in_truancy ;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename md_lasid lasid; rename md_gender gender; rename  ti1_title1_code title1; rename spd_sped_prmy_disb_code spedtype;
rename sln_sped_lvl_need_code spedlev; rename md_days_in_school_susp inschsusp;  rename md_days_out_school_susp 
outschsusp; rename md_days_in_truancy daystruant;
replace gender ="0" if gender=="F" |gender=="f";
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
replace gender="1" if gender=="M" |gender=="m";
order  lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
replace sped="41" if sped=="4i";
gen year=2005;
sort sasid time year;

foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	destring sasid, replace;
compress;
save "`saves'\end05.dta", replace;
clear;
};

if `end06'==1{;
use "`raw'\simseoy06.dta";
rename *, lower ;
keep org_code sasid md_lasid fname mname lname dob md_gender race  rfe town_res school grade attend low_inc
	lep sped spd_sped_prmy_disb_code sln_sped_lvl_need_code ti1_title1_code  md_days_in_school_susp 
	md_days_out_school_susp md_days_in_truancy;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename md_lasid lasid; rename md_gender gender; rename  ti1_title1_code title1; rename spd_sped_prmy_disb_code spedtype;
rename sln_sped_lvl_need_code spedlev; rename md_days_in_school_susp inschsusp;  rename md_days_out_school_susp 
outschsusp; rename md_days_in_truancy daystruant;
order lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
replace sped="41" if sped=="4i";
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen year=2006;
sort sasid time year;
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";

foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;

save "`saves'\end06.dta", replace;
clear;
};

if `end07'==1{;
use "`raw'\simseoy07.dta";
rename *, lower ;
keep org_code sasid md_lasid fname mname lname dob md_gender race  rfe town_res school grade attend low_inc
	lep sped spd_sped_prmy_disb_code sln_sped_lvl_need_code ti1_title1_code  md_days_in_school_susp 
	md_days_out_school_susp md_days_in_truancy;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename md_lasid lasid; rename md_gender gender; rename  ti1_title1_code title1; rename spd_sped_prmy_disb_code spedtype;
rename sln_sped_lvl_need_code spedlev; rename md_days_in_school_susp inschsusp;  rename md_days_out_school_susp 
outschsusp; rename md_days_in_truancy daystruant;
order  lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
replace sped="41" if sped=="4i";
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
replace gender="" if gender=="v"|gender=="V";
gen year=2007;
sort sasid time year;
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";

foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;

compress;
save "`saves'\end07.dta", replace;
clear;
};


if `end08'==1{;
use "`raw'\simseoy08.dta";
rename *, lower ;
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend lowinc grad_core
	lep sped_dis lvl_need title1 in_susp out_susp truancy sped_6to21;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype;
rename lvl_need spedlev; rename in_susp inschsusp;  rename out_susp 
outschsusp; rename truancy daystruant; cap ren town town_res; ren lowinc low_inc; ren sped_6 sped;
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order  lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2008;
sort sasid time year;
foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring grad_core, replace;
compress;
save "`saves'\end08.dta", replace;
clear;
};


if `end09'==1{;
use "`raw'\simseoy09.dta";
rename *, lower ;
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend lowinc grad_core
	lep sped_dis lvl_need title1 in_susp out_susp truancy sped_6;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype;
rename lvl_need spedlev; rename in_susp inschsusp;  rename out_susp 
outschsusp; rename truancy daystruant; ren town town_res; ren lowinc low_inc; ren sped_6 sped;
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order  lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2009;
sort sasid time year;
foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring grad_core, replace;
compress;
save "`saves'\end09.dta", replace;
clear;
};

if `end10'==1{;
use "`raw'\simseoy10.dta";
rename *, lower ;
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend lowinc grad_core cte
	lep sped_dis lvl_need title1 in_susp out_susp truancy sped_6;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype;
rename lvl_need spedlev; rename in_susp inschsusp;  rename out_susp 
outschsusp; rename truancy daystruant; ren town town_res; ren lowinc low_inc; ren sped_6 sped;
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order  lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2010;
sort sasid time year;
foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring grad_core, replace;
compress;
save "`saves'\end10.dta", replace;
clear;
};


if `end11'==1{;
use "`raw'\simseoy11.dta";
rename *, lower ;
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend lowinc grad_core cte
	lep sped_dis lvl_need title1 in_susp out_susp truancy sped_6;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype;
rename lvl_need spedlev; rename in_susp inschsusp;  rename out_susp 
outschsusp; rename truancy daystruant; ren town town_res; ren lowinc low_inc; ren sped_6 sped;
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order  lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code ;
gen time="E";
gen year=2011;
sort sasid time year;
foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring grad_core, replace;
compress;
save "`saves'\end11.dta", replace;
clear;
};


if `end12'==1{;
use "`raw'\simseoy12.dta";
rename *, lower ;
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend lowinc grad_core cte
	lep sped_dis lvl_need title1 in_susp out_susp truancy sped_6;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype;
rename lvl_need spedlev; rename in_susp inschsusp;  rename out_susp 
outschsusp; rename truancy daystruant; ren town town_res; ren lowinc low_inc; ren sped_6 sped;
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order  lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2012;
sort sasid time year;
foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring grad_core, replace;
compress;
save "`saves'\end12.dta", replace;
clear;
};

if `end13'==1{;
use "`raw'\simseoy13.dta";
rename *, lower ;
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend lowinc grad_cor cte
	lep sped_dis lvl_need title1 in_susp out_susp truancy sped_6;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype;
rename lvl_need spedlev; rename in_susp inschsusp;  rename out_susp 
outschsusp; rename truancy daystruant; ren town town_res; ren lowinc low_inc; ren sped_6 sped;
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
*destring DOB;
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order  lasid sasid fname mname lname dob gender race  rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2013;
sort sasid time year;
foreach l of local strings2{;
	destring `l', replace;
	};
foreach l of local missing{;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring grad_cor, replace;
compress;
save "`saves'\end13.dta", replace;
clear;
};


if `end14'==1{;
use "`raw'\simseoy14.dta";
foreach var of varlist * {;
	rename `var' `=lower("`var'")';
};
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend lowinc grad_cor cte
	lep sped_dis lvl_need title1 in_susp out_susp sped_6to truant;

replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype; rename out_susp outschsusp; rename truant daystruant; 
ren town town_res; ren lowinc low_inc; ren sped_6to sped;
rename lvl_need spedlev; rename in_susp inschsusp;   
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order lasid sasid fname mname lname dob gender race rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2014;
sort sasid time year;
foreach l of local strings2 {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring grad_cor, replace;
compress;
save "`saves'\end14.dta", replace;
clear;
};

if `end15'==1{;
use "`raw'\simseoy15.dta";
foreach var of varlist * {;
	rename `var' `=lower("`var'")';
};
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend ecodis grad_core cte
	lep sped_dis lvl_need title1 in_susp out_susp sped_6to truant;

ren ecodis lowinc;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype; rename out_susp outschsusp; rename truant daystruant; 
ren town town_res; ren lowinc low_inc; ren sped_6to sped;
rename lvl_need spedlev; rename in_susp inschsusp;   
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order lasid sasid fname mname lname dob gender race rfe town_res school grade attend low_inc lep sped org_code ;
gen time="E";
gen year=2015;
sort sasid time year;
replace gender = "." if gender=="X";	
foreach l of local strings2 {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring grad_cor, replace;
	ren low_inc ecodis;
compress;
save "`saves'\end15.dta", replace;
clear;
};


if `end16'==1{;
use "`raw'\simseoy16.dta";
foreach var of varlist * {;
	rename `var' `=lower("`var'")';
};
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend ecodis grad_core cte
	lep sped_dis lvl_need title1 in_susp out_susp sped_6to truant;

ren ecodis lowinc;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype; rename out_susp outschsusp; rename truant daystruant; 
ren town town_res; ren lowinc low_inc; ren sped_6to sped;
rename lvl_need spedlev; rename in_susp inschsusp;   
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order lasid sasid fname mname lname dob gender race rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2016;
sort sasid time year;
replace gender = "." if gender=="X";	
foreach l of local strings2 {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring gender, replace;
	cap destring race, replace;
	cap destring race63, replace;
	cap destring town_res, replace;
	cap destring school, replace;
	cap destring grade, replace;
	cap destring low_inc, replace;
	cap destring lep, replace;
	cap destring grad_core, replace;
	cap destring sped, replace;
	cap destring org_code, replace;
	ren low_inc ecodis;
compress;
save "`saves'\end16.dta", replace;

clear;
};

if `end17'==1{;
use "`raw'\simseoy17.dta";
foreach var of varlist * {;
	rename `var' `=lower("`var'")';
};
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend ecodis grad_core cte
	lep sped_dis lvl_need title1 in_susp out_susp sped_6to truant;

ren ecodis lowinc;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype; rename out_susp outschsusp; rename truant daystruant; 
ren town town_res; ren lowinc low_inc; ren sped_6to sped;
rename lvl_need spedlev; rename in_susp inschsusp;   
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order lasid sasid fname mname lname dob gender race rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2017;
sort sasid time year;
replace gender = "." if gender=="X";
replace gender = "." if gender=="N";	
foreach l of local strings2 {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring gender, replace;
	cap destring race, replace;
	cap destring race63, replace;
	cap destring town_res, replace;
	cap destring school, replace;
	cap destring grade, replace;
	cap destring low_inc, replace;
	cap destring lep, replace;
	cap destring sped, replace;
	cap destring grad_core, replace;
	cap destring org_code, replace;
	ren low_inc ecodis;
compress;
save "`saves'\end17.dta", replace;
clear;
};
if `end18'==1{;
use "`raw'\simseoy18.dta";
foreach var of varlist * {;
	rename `var' `=lower("`var'")';
};
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend ecodis grad_core cte
	lep sped_dis lvl_need title1 in_susp out_susp sped_6to truant;

ren ecodis lowinc;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype; rename out_susp outschsusp; rename truant daystruant; 
ren town town_res; ren lowinc low_inc; ren sped_6to sped;
rename lvl_need spedlev; rename in_susp inschsusp;   
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order lasid sasid fname mname lname dob gender race rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2018;
sort sasid time year;
replace gender = "." if gender=="X";
replace gender = "." if gender=="N";	
foreach l of local strings2 {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring gender, replace;
	cap destring race, replace;
	cap destring race63, replace;
	cap destring town_res, replace;
	cap destring school, replace;
	cap destring grade, replace;
	cap destring low_inc, replace;
	cap destring lep, replace;
	cap destring sped, replace;
	cap destring grad_core, replace;
	cap destring org_code, replace;
	ren low_inc ecodis;
compress;
save "`saves'\end18.dta", replace;
clear;
};
if `end19'==1{;
use "`raw'\simseoy19.dta";
foreach var of varlist * {;
	rename `var' `=lower("`var'")';
};
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend ecodis grad_core cte
	lep sped_dis lvl_need title1 /*in_susp out_susp */ sped_6to truant;

ren ecodis lowinc;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype; /* rename out_susp outschsusp; J*/rename truant daystruant; 
ren town town_res; ren lowinc low_inc; ren sped_6to sped;
rename lvl_need spedlev; /*rename in_susp inschsusp;  */ 
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order lasid sasid fname mname lname dob gender race rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2019;
sort sasid time year;
replace gender = "." if gender=="X";
replace gender = "." if gender=="N";	
foreach l of local strings2 {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring gender, replace;
	cap destring race, replace;
	cap destring race63, replace;
	cap destring town_res, replace;
	cap destring school, replace;
	cap destring grade, replace;
	cap destring low_inc, replace;
	cap destring lep, replace;
	cap destring sped, replace;
	cap destring grad_core, replace;
	cap destring org_code, replace;
	ren low_inc ecodis;
compress;
save "`saves'\end19.dta", replace;
clear;
};
if `end20'==1{;
use "`raw'\simseoy20.dta";
foreach var of varlist * {;
	rename `var' `=lower("`var'")';
};
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend ecodis grad_core cte
	lep sped_dis lvl_need title1 /*in_susp out_susp */ sped_6to truant;

ren ecodis lowinc;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype; /* rename out_susp outschsusp; J*/rename truant daystruant; 
ren town town_res; ren lowinc low_inc; ren sped_6to sped;
rename lvl_need spedlev; /*rename in_susp inschsusp;  */ 
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order lasid sasid fname mname lname dob gender race rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2020;
sort sasid time year;
replace gender = "." if gender=="X";
replace gender = "." if gender=="N";	
foreach l of local strings2 {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring gender, replace;
	cap destring race, replace;
	cap destring race63, replace;
	cap destring town_res, replace;
	cap destring school, replace;
	cap destring grade, replace;
	cap destring low_inc, replace;
	cap destring lep, replace;
	cap destring sped, replace;
	cap destring grad_core, replace;
	cap destring org_code, replace;
	ren low_inc ecodis;
compress;
save "`saves'\end20.dta", replace;
clear;
};
if `end21'==1{;
use "`raw'\simseoy21.dta";
foreach var of varlist * {;
	rename `var' `=lower("`var'")';
};
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend ecodis grad_core cte
	lep sped_dis lvl_need title1 /*in_susp out_susp */ sped_6to truant;

ren ecodis lowinc;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype; /* rename out_susp outschsusp; J*/rename truant daystruant; 
ren town town_res; ren lowinc low_inc; ren sped_6to sped;
rename lvl_need spedlev; /*rename in_susp inschsusp;  */ 
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen dob1=date(dob, "MDY", 2021);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order lasid sasid fname mname lname dob gender race rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2018;
sort sasid time year;
replace gender = "." if gender=="X";
replace gender = "." if gender=="N";	
foreach l of local strings2 {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring gender, replace;
	cap destring race, replace;
	cap destring race63, replace;
	cap destring town_res, replace;
	cap destring school, replace;
	cap destring grade, replace;
	cap destring low_inc, replace;
	cap destring lep, replace;
	cap destring sped, replace;
	cap destring grad_core, replace;
	cap destring org_code, replace;
	ren low_inc ecodis;
compress;
save "`saves'\end21.dta", replace;
clear;
};
if `end22'==1{;
use "`raw'\simseoy22.dta";
foreach var of varlist * {;
	rename `var' `=lower("`var'")';
};
keep org_code sasid lasid fname mname lname dob gender race rfe town school grade attend ecodis grad_core cte
	lep sped_dis lvl_need title1 /*in_susp out_susp */ sped_6to truant;

ren ecodis lowinc;
replace grade="0" if grade=="KP"|grade=="KF"|grade=="KT";
replace grade="-1" if grade=="PK";
replace grade ="13" if grade=="SP";
replace grade ="" if grade=="PG" | grade=="AO"|grade=="CT";
rename sped_dis spedtype; /* rename out_susp outschsusp; J*/rename truant daystruant; 
ren town town_res; ren lowinc low_inc; ren sped_6to sped;
rename lvl_need spedlev; /*rename in_susp inschsusp;  */ 
replace gender ="0" if gender=="F" |gender=="f";
replace gender="1" if gender=="M" |gender=="m";
gen dob1=date(dob, "MDY", 2012);
drop dob ;
rename dob1 dob;
format dob %td;
*all caps word variables to facilitate matching;
foreach v of varlist fname lname mname   {;
	replace `v' = upper(trim(`v'));
	};
*NMN;
replace mname="" if mname=="NMN";
order lasid sasid fname mname lname dob gender race rfe town_res school grade attend low_inc lep sped org_code;
gen time="E";
gen year=2022;
sort sasid time year;
replace gender = "." if gender=="X";
replace gender = "." if gender=="N";	
foreach l of local strings2 {;
	destring `l', replace;
	};
foreach l of local missing {;
	replace `l'=. if `l'==555;
	};
	ren race race63;
	destring sasid, replace;
	cap destring gender, replace;
	cap destring race, replace;
	cap destring race63, replace;
	cap destring town_res, replace;
	cap destring school, replace;
	cap destring grade, replace;
	cap destring low_inc, replace;
	cap destring lep, replace;
	cap destring sped, replace;
	cap destring grad_core, replace;
	cap destring org_code, replace;
	ren low_inc ecodis;
compress;
save "`saves'\end22.dta", replace;
clear;
};